﻿using sMES_Base;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using sMES_Base.service;
using machine_checklist_get.ORM;
using machine_checklist_get.Models;
using System.Data;
using Module_EQP.Models;
using machine_parameter_info_get.Models;

namespace machine_checklist_get
{
    /// <summary>
	/// 元件[machine_checklist_get]
    /// <auth>gracecai</auth><time>2018/11/26 下午 03:37:57</time>
    /// <description></description>
    /// <list>
    ///     <modify>
    ///         <auth></auth><mantis></mantis><time></time>
    ///         <description></description>
    ///     </modify>
    /// </list>
    /// </summary>
    public class machine_checklist_get_utility : BaseUtility
    {
        public machine_checklist_get_utility(ref BaseService CurrentService) : base(ref CurrentService)
        {
        }
        /// <summary>
        /// 機台點檢表取得
        /// </summary>
        /// <param name="content">RequestContent</param>
        /// <returns>機台點檢表取得</returns>
        public List<ORM_machine_checklist_get> GetMachineChecklist(REQ_machine_checklist_get content)
        {
            List<ORM_machine_checklist_get> _result = new List<ORM_machine_checklist_get>();

            ORM_machine_checklist_get detail=new ORM_machine_checklist_get();
            #region 取出設備集成參數資訊
            machine_parameter_info_get.Models.RES_machine_parameter_info_get _res_content = new machine_parameter_info_get.Models.RES_machine_parameter_info_get();
            StringBuilder _sql = new StringBuilder();
            _sql.Append(" SELECT C.DataitemNO, C.ParamNO ");
            _sql.Append(" FROM tblEQPQCListDetail A ");
            _sql.Append("   JOIN tblEQPEquipmentBasis B ON A.QCListNo = B.QCListNo AND B.EquipmentNo = '" + content.machine_no + "' ");
            _sql.Append("   JOIN TBLESIEQPParam C ON B.EQUIPMENTNO = C.EquipmentNO AND A.DataitemNO = C.DataitemNO ");
            _sql.Append(" WHERE A.AutoDataQuery = 1 ");

            DataTable _params = CurrentService.GetDao().Query(_sql.ToString());
            if (_params.Rows.Count > 0) {
                REQ_machine_parameter_info_get _req_content = new REQ_machine_parameter_info_get();
                _req_content.equipment_no = content.machine_no;
                _req_content.machine_parameters = new List<CDO_machine_parameter>();
                foreach (DataRow _param in _params.Rows) {
                    _req_content.machine_parameters.Add(new CDO_machine_parameter()
                    {
                        parameter_no = Convert.ToString(_param["ParamNO"])
                    });
                }
                _res_content = this.CurrentService.GetUtilities<machine_parameter_info_get.machine_parameter_info_get_utility>().GetParameter(_req_content);
            }
            #endregion

            #region 取出設備點檢資訊
            _sql = new StringBuilder("");
            //_sql.Append(" SELECT a.QcListNo AS checklist_no, ");
            //_sql.Append("        c.QCLISTNAME AS checklist_name, ");
            //_sql.Append("        a.QcOrder AS item_no, ");
            //_sql.Append("        a.QcOrder AS seq, ");
            //_sql.Append("        a.QcItem AS check_item, ");
            //_sql.Append("        a.QcType+1 AS check_type, ");
            //_sql.Append("        a.StdValue AS std_value, ");
            //_sql.Append("        a.MinIvalue AS range_min, ");
            //_sql.Append("        a.MaxIvalue AS range_max, ");
            //_sql.Append("        a.InspectionMethod AS check_method, ");
            //_sql.Append("        a.InspectionStandards AS check_standard, ");
            //// 20190610 modify by Dustdusk for M#57084:增加[採集順序 ParamNO]、[資料項編號 DataitemNO]、[下询资料项 AutoDataQuery]
            //_sql.Append("        NVL(d.ParamNO, '') AS param_no, "); 
            //_sql.Append("        NVL(d.DataitemNO, '') AS data_item_no, ");
            //_sql.Append("        CASE WHEN d.DataitemNO is null then 'N' else 'Y' end AS is_param, ");
            //_sql.Append("        a.AutoDataQuery as auto_data_query");
            //_sql.Append(" FROM tblEQPQCListDetail a ");
            //_sql.Append("   JOIN tblEQPEquipmentBasis b ON a.QCListNo = b.QCListNo ");
            //_sql.Append("                              AND b.EquipmentNo = '" + content.machine_no + "' ");
            //_sql.Append("   JOIN tblEQPQCListBasis c ON a.QcListNo = c.QCLISTNO ");
            //_sql.Append(" LEFT JOIN TBLESIEQPParam d ON b.EQUIPMENTNO = d.EquipmentNO AND a.DataitemNO = d.DataitemNO");
            //	0064435: 設備點檢頻率後台服務開發
            _sql.AppendLine("Select ");
            //20210609 Modify by德義家興for#91011: 
            _sql.AppendLine("       cf.EquipmentCheckUpRate, ");
            _sql.AppendLine("       cf.QcListNo AS checklist_no, ");
            _sql.AppendLine("       qcl.QcListName AS checklist_name,");
            _sql.AppendLine("       det.QcOrder AS item_no,");
            _sql.AppendLine("       det.QcOrder AS seq,");
            _sql.AppendLine("       det.QcItem AS check_item,");
            _sql.AppendLine("       det.QcType+1 AS check_type,");
            _sql.AppendLine("       det.StdValue AS std_value,");
            _sql.AppendLine("       det.MinIvalue AS range_min,");
            _sql.AppendLine("       det.MaxIvalue AS range_max,");
            _sql.AppendLine("       det.InspectionMethod AS check_method,");
            _sql.AppendLine("       det.InspectionStandards AS check_standard,");
            _sql.AppendLine("       NVL(pa.ParamNO, '') AS param_no,");
            _sql.AppendLine("       NVL(pa.DataitemNO, '') AS data_item_no,");
            _sql.AppendLine("       CASE WHEN pa.DataitemNO is null then 'N' else 'Y' end AS is_param,");
            _sql.AppendLine("       det.AutoDataQuery as auto_data_query ");
            _sql.AppendLine("  From tblEQPEquipmentCheckUpRate cf ");
            _sql.AppendLine("  Join tblEQPEquipmentbasis eq On cf.EquipmentNo = eq.EquipmentNo ");
            _sql.AppendLine("  Join tblEQPQCLISTBASIS qcl On cf.QcListNo = qcl.QCListNo");
            _sql.AppendLine("  Join tblEQPQCLISTDETAIL det On qcl.QCLISTNO = det.QCLISTNO ");
            _sql.AppendLine("  Left Join TBLESIEQPParam pa ON eq.EQUIPMENTNO = pa.EquipmentNO AND det.DataitemNO = pa.DataitemNO");
            _sql.AppendLine(" Where cf.EquipmentNo = '" + content.machine_no + "'");

            if (content.qc_frequency != "-1") 
            {
                _sql.AppendLine("   And cf.EquipmentCheckUpRate = " + content.qc_frequency + "");
            }

            detail.machine_checklist_get_checklist_detail = CurrentService.GetDao().Query<ORM_machine_checklist_get_checklist_detail>(_sql.ToString());
            if (detail.machine_checklist_get_checklist_detail.Count > 0) {
                for(int i =0;i < detail.machine_checklist_get_checklist_detail.Count;i++) {
                    ORM_machine_checklist_get_checklist_detail _row = detail.machine_checklist_get_checklist_detail[i];
                    if (i == 0) {
                        detail.checklist_no = _row.checklist_no;/*'點檢表編號*/
                        detail.checklist_name = _row.checklist_name; /*'點檢表名稱*/
                        detail.checklist_display = _row.checklist_name + "(" + _row.checklist_no + ")"; /*'點檢表名稱*/
                    }

                    if (_row.auto_data_query == "1" && _res_content != null && _res_content.machine_parameters != null && _res_content.machine_parameters.Count > 0) {
                        CDO_machine_parameter _param = _res_content.machine_parameters.Find((machine_parameter) => {
                            return machine_parameter.parameter_no == _row.param_no;
                        });
                        if(_param!=null)
                            _row.check_value = _param.value;
                    }
                };
               
            } else {
                //Exception: 尚未設定檢點作業
                throw new ServiceException(ServiceCode.ParamsValidateException, CurrentService.GetResource("[%Module_EQP.Not_Set_up_QCList%]"));
            }
            #endregion
            _result.Add(detail);
            return _result;
        }
        //20210609 Modify by德義家興for#91011: 
        public RES_machine_checklist_get GetMultiSelectedEquipmentCheckUprate(REQ_machine_checklist_get content)
        {
            List<ORM_qclistno_get> qclist = new List<ORM_qclistno_get>();
            ORM_machine_checklist_get detail = new ORM_machine_checklist_get();
            RES_machine_checklist_get result = new RES_machine_checklist_get();

            //組合已選的設備編號
            string selected_EquipmentList = "('";
            foreach ( string Equipment in content.selected_EquipmentList)
            {
                selected_EquipmentList += Equipment + "','";
            }
            selected_EquipmentList = selected_EquipmentList.Substring(0, selected_EquipmentList.Length - 2) + ")";

            StringBuilder _sql = new StringBuilder();
            //檢查是否對應不同的點點檢單號
            //query
            _sql.Append(" select EquipmentCheckUpRate, count(1) doube ");
            _sql.Append(" from( ");
            _sql.Append(" select EquipmentCheckUpRate, QcListNo ");
            _sql.Append(" from tblEQPEquipmentCheckUpRate ");
            _sql.Append(" where EquipmentNo IN " + selected_EquipmentList + " ");
            _sql.Append(" AND IsEnabled = 1 ");
            _sql.Append(" group by EquipmentCheckUpRate, QcListNo ");
            _sql.Append(" ) x ");
            _sql.Append(" group by EquipmentCheckUpRate having count(1)> 1 ");
            DataTable isCorrespondsDifferent = CurrentService.GetDao().Query(Convert.ToString(_sql));
            if (isCorrespondsDifferent.Rows.Count > 0)
            {
                //设备存在不一致的点检单号
                throw new ServiceException(ServiceCode.ParamsValidateException, CurrentService.GetResource("[%Module_EQP.Equipment_Has_Inconsistent_QCList%]"));
            }

            //query2
            _sql = new StringBuilder("");
            _sql.Append(" select EquipmentCheckUpRate, QcListNo ");
            _sql.Append(" from tblEQPEquipmentCheckUpRate ");
            _sql.Append(" where EquipmentNo IN " + selected_EquipmentList + " "); 
            _sql.Append(" AND IsEnabled = 1 ");
            _sql.Append(" group by EquipmentCheckUpRate,QcListNo ");
            qclist = CurrentService.GetDao().Query<ORM_qclistno_get>(Convert.ToString(_sql));
            //抛出提醒“未设置点检资讯:
            string msg = query3(qclist, selected_EquipmentList);
            //塞grid的值
            _sql = new StringBuilder("");
            _sql.Append(" Select DISTINCT ");
            _sql.Append(" cf.EquipmentCheckUpRate, ");
            _sql.Append(" cf.QcListNo checklist_no, ");
            _sql.Append(" qcl.QcListName checklist_name, ");
            _sql.Append(" det.QcOrder item_no, ");
            _sql.Append(" det.QcOrder seq, ");
            _sql.Append(" det.QcItem check_item, ");
            _sql.Append(" det.QcType + 1  check_type, ");
            _sql.Append(" det.StdValue std_value, ");
            _sql.Append(" det.MinIvalue range_min, ");
            _sql.Append(" det.MaxIvalue range_max, ");
            _sql.Append(" det.InspectionMethod check_method, ");
            _sql.Append(" det.InspectionStandards check_standard, ");
            _sql.Append(" NVL(pa.ParamNO, '')  param_no, ");
            _sql.Append(" NVL(pa.DataitemNO, '')  data_item_no, ");
            _sql.Append(" CASE WHEN pa.DataitemNO is null then 'N' else 'Y' end is_param, ");
            _sql.Append(" det.AutoDataQuery auto_data_query ");
            _sql.Append(" From tblEQPEquipmentCheckUpRate cf ");
            _sql.Append(" Join tblEQPEquipmentbasis eq On cf.EquipmentNo = eq.EquipmentNo ");
            _sql.Append(" Join tblEQPQCLISTBASIS qcl On cf.QcListNo = qcl.QCListNo ");
            _sql.Append(" Join tblEQPQCLISTDETAIL det On qcl.QCLISTNO = det.QCLISTNO ");
            _sql.Append(" Left Join TBLESIEQPParam pa ON eq.EQUIPMENTNO = pa.EquipmentNO AND det.DataitemNO = pa.DataitemNO ");
            _sql.Append(" Where cf.EquipmentNo in " + selected_EquipmentList + " ");
            _sql.Append(" And cf.IsEnabled = 1 ");

            detail.machine_checklist_get_checklist_detail = CurrentService.GetDao().Query<ORM_machine_checklist_get_checklist_detail>(Convert.ToString(_sql));
            if (detail.machine_checklist_get_checklist_detail.Count > 0)
            {
                for (int i = 0; i < detail.machine_checklist_get_checklist_detail.Count; i++)
                {
                    ORM_machine_checklist_get_checklist_detail _row = detail.machine_checklist_get_checklist_detail[i];
                    if (i == 0)
                    {
                        detail.checklist_no = _row.checklist_no;/*'點檢表編號*/
                        detail.checklist_name = _row.checklist_name; /*'點檢表名稱*/
                        detail.checklist_display = _row.checklist_name + "(" + _row.checklist_no + ")"; /*'點檢表名稱*/
                    }
                }
            }
            else
            {
                //Exception: 尚未設定檢點作業
                throw new ServiceException(ServiceCode.ParamsValidateException, CurrentService.GetResource("[%Module_EQP.Not_Set_up_QCList%]"));
            }

            List<ORM_machine_checklist_get> temp = new List<ORM_machine_checklist_get>();
            temp.Add(detail);
            result.machine_checklist_get_detail = temp;
            result.check_msg = msg;
            return result;
        }
        
        private string query3(List<ORM_qclistno_get> qclist, string selected_EquipmentList)
        {
            //query3
            string msg = string.Empty;
            StringBuilder _sql;
            for (int i = 0; i < qclist.Count; i++)
            {
                _sql = new StringBuilder("");
                _sql.Append(" select count(*) ");
                //20210609 Modify by德義家興for#91011: 修正檢查邏輯中的table
                _sql.Append(" from TBLEQPQCLISTDETAIL a ");
                _sql.Append(" where QCLISTNO = '" + qclist[i].QcListNo + "' ");
                //_sql.AppendLine(" where QCLISTNO = '" + "errorTest" + "' ");
                string query3 = CurrentService.GetDao().Query1RecordString(Convert.ToString(_sql));

                if (Convert.ToInt32(query3) == 0)
                    msg +=  qclist[i].QcListNo + ", ";
            }

            if (!(string.IsNullOrEmpty(msg)))
                msg = msg.Substring(0, msg.Length - 2);

            return msg;
        }

        public string GetMachineLastHandTime(REQ_machine_checklist_get content)
        {
            List<string> listDate = CurrentService.GetDao().Query<string>($" select handle_time_C from tblWIPEQPQCListLog where EquipmentNo = '{content.machine_no}' order by handle_time_C desc  ");
            if (listDate.Count > 0)
                return listDate[0];
            else
                return "";
        }

    }
}
